( Read more... )

It occurs to me I forgot to congratulate the winners of the free ebooks. So without further ado:

• SAB, who seems to host a nice blog geared toward server administration.
• Stephan, who’s looking to improve existing strategies.
• Jeff and his growing PostgreSQL cluster.
• Pierre, who apparently has an experimental PostgreSQL backend for MySQL. Interesting.

( Read more... )

Let’s face it, the pg_hba.conf file is a pain in the ass to use regularly. Sure, reloading the database will cause it to re-read this file, but with a lot of active users and frequent changes, this isn’t really tenable.

Luckily lurking deep within its bowels, PostgreSQL has a little-known feature that can easily be overlooked because it’s so ( Read more... )

A little while ago, I wrote to the PostgreSQL general mailing list that I’d been approached by Packt Publishing to contribute a quick manual on doing PostgreSQL backups: Instant PostgreSQL Backup and Restore How-to. They’re the same guys who published Greg Smith’s PostgreSQL 9.0 High Performance book which everyone seems to swear by.

The goal of ( Read more... )

Well, that was an interesting couple of days. Unfortunately my birthday came right after and I didn’t feel like writing anything for the duration. Now though? Why not!

In the end, I think my presentation, NVRam for Fun and Profit went over okay. Not a ton of people showed up, but I did get ambushed afterwards with questions after I got off the ( Read more... )

A few months ago, Greg Smith of PostgreSQL fame suggested I submit a proposal to the new Postgres Open conference here in Chicago. Some of us residents of the Midwest have long waited for a PostgreSQL-related conference of our very own, and now the glorious day has finally arrived. I was asked to submit proposals to other conferences, but the ( Read more... )

Ah, query hints. For all those times when the database isn’t doing what you want, they’re a useful tool for forcing the query optimizer to perform your bidding. But in this case, not only is the road to Hell paved with good intentions, it’s paved with a frictionless slide directly into a wood chipper that empties handily into an active volcano. ( Read more... )

So you’ve decided to use PostgreSQL as the database for your sparkly new website running some variant of a LAMP stack. Or maybe you just got a new job and must now administer a PostgreSQL install so you excitedly did your research. You’ve read the install docs, tinkered on a VM, and you think you’ve got everything ready.

You’re wrong. You’re going ( Read more... )

With the introduction of PostgreSQL 8.4, Bruce Momjian, a significant core developer, contributed a tool that can actually upgrade an entire database cluster in place. The time required is essentially only that necessary to copy the data files from the old installation to the new one. On a quick RAID system, this can be an order of magnitude faster ( Read more... )

PostgreSQL can be both a beautiful thing, and an infuriating mess.

Occasionally I look through the logs on one of our database servers to see if I can’t optimize some queries. It’s good practice, and is an excellent way to monitor basic system performance by watching for queries that take longer than might otherwise be possible. Sometimes ( Read more... )